# -*- encoding:utf-8 -*-
# !/usr/bin/env python

import json
from flask import Blueprint, g, make_response, send_file
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
from config import config
from lib.table import table
from lib import auth
from lib.validform import V, ValidateForm
from lib.exportlog import makeCsv
from openpyxl import Workbook

app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 获取筛选账号,用户名
@app.route('/api/business/operatelog/filter', methods=['POST'])
@auth.permission('logger', 'readOnly')
def get_system_logger_filter():
    try:
        data = {}
        g.cursor.execute("""select b.user_name as text, b.user_name as value from sys_operate_log a
                            left join sys_login b on b.login_id = a.login_id group by b.user_name""")
        data['user_name'] = g.cursor.fetchall()
        g.cursor.execute("""select c.person_name as text, c.person_name as value from sys_operate_log a
                            left join sys_login b on b.login_id = a.login_id left join sys_person c
                            on c.person_id = b.person_id group by c.person_name""")
        data['person_name'] = g.cursor.fetchall()
        return json.dumps({"status": "success", "data": data})
    except Exception, e:
        return json.dumps({"status": "except", "msg": str(e)})


# 获取系统日志
@app.route('/api/business/operatelog', methods=['POST'])
@auth.permission('logger', 'readOnly')
def get_system_logger():
    try:
        tb = table()
        g.cursor.execute("""select count(1) as num
                            from sys_operate_log a
                            left join sys_login b on b.login_id = a.login_id
                            left join sys_person c on c.person_id = b.person_id
                            %s""" % tb.where(['b.user_name', 'c.person_name', 'a.remark']))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""select a.operate_id as key, d2t(a.operate_time) as aoperate_time,
                                    a.operate_type as aoperate_type, a.remark as aremark, a.operate_fun as aoperate_fun, split_part(a.login_ip::varchar,'/',1) as alogin_ip,
                                    b.user_name as buser_name,c.person_name as cperson_name
                             from sys_operate_log a
                             left join sys_login b
                             on b.login_id = a.login_id
                             left join sys_person c
                             on c.person_id = b.person_id
                             %s %s %s %s""" % (tb.where(['b.user_name', 'c.person_name', 'a.remark']), tb.orderBy(), tb.offset(), tb.limit()))
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": g.cursor.fetchall()
            }
        })
    except Exception, e:
        return json.dumps({"status": "except", "msg": str(e)})


# 获取设置时间
@app.route('/api/business/operatelog/deadline', methods=['GET'])
@auth.permission('logger', 'readOnly')
def getdeadline():
    try:
        g.cursor.execute("""select deadline from h_operate_log_deadline where deadline_id = 1""")
        return json.dumps({
            "status": "success",
            "data": {
                "msg": '获取成功',
                "deadline": g.cursor.fetchall()
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '获取失败'})


# 更新deadline设置
@app.route('/api/business/operatelog/updatadeadline', methods=['POST'])
@auth.permission('logger')
def updatedeadline(_currUser):
    try:
        form = ValidateForm(
            deadline=['deadline', V.required()],
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        g.cursor.execute("""update h_operate_log_deadline
                            set deadline = '%(deadline)s', r_time = now() where deadline_id = 1 """ % (data))
        g.conn.commit()
        auth.logsync(_currUser, {"function": "操作日志", "type": "修改", "remark": "修改保存期限"})
        return json.dumps({"status": "success", "msg": "修改成功"})
    except Exception:
        return json.dumps({"status": "except", 'msg': '修改失败'})


# 导出excel文件
@app.route('/api/business/operatelog/exportexcel', methods=['POST'])
@auth.permission('logger', 'readOnly')
def exportexcel():
    try:
        tb = table()
        list_msg = []
        g.cursor.execute("""select a.operate_id as key, d2t(a.operate_time) as aoperate_time,
                                    a.operate_type as aoperate_type, a.remark as aremark, a.operate_fun as aoperate_fun, split_part(a.login_ip::varchar,'/',1) as alogin_ip,
                                    b.user_name as buser_name,c.person_name as cperson_name
                                from sys_operate_log a
                                left join sys_login b
                                on b.login_id = a.login_id
                                left join sys_person c
                                on c.person_id = b.person_id
                                %s %s """ % (tb.where(['b.user_name', 'c.person_name']), tb.orderBy()))
        rows = g.cursor.fetchall()
        list_msg = []
        for i in rows:
            list_msg.append([])
        for key, value in enumerate(rows):
            list_msg[key].append(value["aoperate_time"])
            list_msg[key].append(value["buser_name"])
            list_msg[key].append(value["cperson_name"])
            list_msg[key].append(value["aoperate_fun"])
            list_msg[key].append(value["aoperate_type"])
            list_msg[key].append(value["aremark"])
            list_msg[key].append(value["alogin_ip"])
        wb = Workbook()
        ws = wb.active
        ws.append([u'记录时间', u'账号', u'用户姓名', u'功能模块', u'操作', u'内容描述', u'客户端IP'])
        dataLen = len(list_msg)
        for i in range(0, dataLen):
            ws.append(list_msg[i])
        filepath = config.OPERATE_DOWNLOAD_EXCEL
        wb.save(filepath)
        response = make_response(send_file(filepath))
        response.headers["Content-Disposition"] = "attachment; filename=%s;" % 'operatelogExcel.xlsx'
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "下载失败"})


# 导出csv文件
@app.route('/api/business/operatelog/exportcsv', methods=['POST'])
@auth.permission('logger', 'readOnly')
def exportcsv():
    try:
        tb = table()
        list_msg = []
        g.cursor.execute("""select a.operate_id as key, d2t(a.operate_time) as aoperate_time,
                                    a.operate_type as aoperate_type, a.remark as aremark, a.operate_fun as aoperate_fun, split_part(a.login_ip::varchar,'/',1) as alogin_ip,
                                    b.user_name as buser_name,c.person_name as cperson_name
                                from sys_operate_log a
                                left join sys_login b
                                on b.login_id = a.login_id
                                left join sys_person c
                                on c.person_id = b.person_id
                                %s %s """ % (tb.where(['b.user_name', 'c.person_name']), tb.orderBy()))
        rows = g.cursor.fetchall()
        list_msg = []
        for i in rows:
            list_msg.append([])
        for key, value in enumerate(rows):
            list_msg[key].append(value["aoperate_time"])
            list_msg[key].append(value["buser_name"])
            list_msg[key].append(value["cperson_name"])
            list_msg[key].append(value["aoperate_fun"])
            list_msg[key].append(value["aoperate_type"])
            list_msg[key].append(value["aremark"])
            list_msg[key].append(value["alogin_ip"])
        makeCsv(list_msg)
        response = make_response(send_file(config.OPERATE_DOWNLOAD_CSV))
        response.headers["Content-Disposition"] = "attachment; filename=%s;" % 'operatelogCsv.csv'
        return response
    except Exception:
        return json.dumps({"status": "fail", "msg": "下载失败"})
